#include "sqliteCommon.h"

#define DB_PATH "/opt/Tim/db"
#define PIC_DB_PATH "/opt/Tim/db/pic.db"

static sqlite3 *db;
static bool isMkDbDir = false;
void openPicDb()
{
    if (isMkDbDir == false)
    {
        bool result = mkdirCommon(DB_PATH);
        if (result == false)
        {
            LOG_ERROR("创建数据库目录失败");
            abort();
            return;
        }
        isMkDbDir = true;
    }

    sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
    // sqlite3_config(SQLITE_CONFIG_SERIALIZED);
    int32_t rc;
    rc = sqlite3_open(PIC_DB_PATH, &db);

    if (rc)
    {
        LOG_ERROR("无法打开数据库: %s", sqlite3_errmsg(db));
        abort();
    }
    else
    {
        LOG_INFO("数据库打开成功");
    }
}

#define TABLE_COL_PIC_NAME "picName"
#define TABLE_COL_LICENSE "license"
#define TABLE_COL_DEV_NO "devNo"
#define TABLE_COL_SHOT_TIME_BCD "shotTimeBcd"
#define TABLE_COL_SHOT_TIME "shotTime"
#define TABLE_COL_PARK_NO "parkNo"
#define TABLE_COL_CAM_ID "camId"
#define TABLE_COL_FILE_NO "fileNo"
#define TABLE_COL_GGA "gga"
#define TABLE_COL_RMC "rmc"
#define TABLE_COL_CAR_LOCATION_NO "carLocationNo"
#define TABLE_COL_IS_UPLOADED "isUploaded"
#define TABLE_COL_PIC_TYPE "picType"
#define TABLE_COL_IS_DELETE "isDelete"
/**
 * 0 默认
 * > 0, 发送次数
*/
#define TABLE_COL_RETRY_COUNTS "retryCounts"

#define PIC_DB_TABLE_NAME "PicDb"

void createPicDB()
{
    uint8_t sql[1024] = {0};
    sprintf(sql,
            "CREATE TABLE IF NOT EXISTS  %s("
            "%s TEXT    PRIMARY KEY     NOT NULL,"    // 图片名称
            "%s TEXT    NOT NULL,"                    // 车牌号码
            "%s BLOB    NOT NULL,"                    // 设备编码
            "%s BLOB    NOT NULL,"                    // 图片拍照时间bcd
            "%s DATE    NOT NULL,"                    // 图片拍照时间
            "%s BLOB,"                                // 停车场编码
            "%s INT     NOT NULL,"                    // 相机编码
            "%s INT     NOT NULL,"                    // 文件编码（与相机唯一标识一一对应）
            "%s TEXT    NOT NULL,"                    // gga
            "%s TEXT    NOT NULL,"                    // rmc
            "%s TEXT,"                                // 车位编号
            "%s BOOLEAN NOT NULL        DEFAULT 0,"   // 是否上传
            "%s NUMERIC NOT NULL        DEFAULT 0,"   // 图片类型：0小图 1大图 2标定图片
            "%s BOOLEAN NOT NULL        DEFAULT 0,"   // 是否删除
            "%s INT     NOT NULL        DEFAULT 0);", // 是否大图
            PIC_DB_TABLE_NAME,
            TABLE_COL_PIC_NAME, TABLE_COL_LICENSE, TABLE_COL_DEV_NO, TABLE_COL_SHOT_TIME_BCD, TABLE_COL_SHOT_TIME,
            TABLE_COL_PARK_NO, TABLE_COL_CAM_ID, TABLE_COL_FILE_NO, TABLE_COL_GGA, TABLE_COL_RMC,
            TABLE_COL_CAR_LOCATION_NO, TABLE_COL_IS_UPLOADED, TABLE_COL_PIC_TYPE, TABLE_COL_IS_DELETE,
            TABLE_COL_RETRY_COUNTS);

    /* Execute SQL statement */
    int32_t rc;
    char *zErrMsg;

    rc = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if (rc != SQLITE_OK)
    {
        LOG_ERROR("SQL 执行错误: %s", zErrMsg);
        sqlite3_free(zErrMsg);
        abort();
    }
    else
    {
        LOG_INFO("表创建成功");
    }
}

void insertPicDb(uint8_t picName[PIC_FILENAME_MAX_LEN], uint8_t license[TH_LP_LEN], uint8_t camId, uint8_t fileNo, uint8_t picTimeBcd[BCD_TIME_LENGTH], gps_t gps, uint8_t picType)
{
    if (!(picType == SMALL_PIC_TYPE || picType == BIG_PIC_TYPE || picType == CALIBRATION_PIC_TYPE))
    {
        LOG_ERROR("图片类型不正确");
        abort();
    }

    uint8_t picTimeStr[DATE_TIME_STRING_LENGTH] = {0};
    bcd_time_to_string(picTimeBcd, picTimeStr);

    uint8_t sql[1024];
    sprintf(sql,
            "INSERT INTO %s (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) " //
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, %d );",                 //
            PIC_DB_TABLE_NAME,
            TABLE_COL_PIC_NAME, TABLE_COL_LICENSE, TABLE_COL_DEV_NO, TABLE_COL_SHOT_TIME_BCD, TABLE_COL_SHOT_TIME,
            TABLE_COL_CAM_ID, TABLE_COL_FILE_NO, TABLE_COL_GGA, TABLE_COL_RMC,
            TABLE_COL_PIC_TYPE,
            picType);

    int32_t rc;
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
    if (rc != SQLITE_OK)
    {
        LOG_ERROR("数据库语句创建失败： %s", sqlite3_errmsg(db));
        goto END;
    }
    {
        uint8_t ggaTmp[MINMEA_MAX_LENGTH + 1] = {0};
        uint8_t rmcTmp[MINMEA_MAX_LENGTH + 1] = {0};

        memcpy(ggaTmp, gps.gps_gngga_data, MINMEA_MAX_LENGTH);
        memcpy(rmcTmp, gps.gps_gnrmc_data, MINMEA_MAX_LENGTH);

        uint8_t nParm = 1;
        uint8_t *chedaojiNo = getChedaojiNo();
        // picName
        sqlite3_bind_text(stmt, nParm++, picName, strlen(picName), SQLITE_TRANSIENT);
        // license
        if (picType == CALIBRATION_PIC_TYPE)
        {
            // 车牌不为空，先插入0
            sqlite3_bind_text(stmt, nParm++, "0", strlen("0"), SQLITE_TRANSIENT);
        }
        else
        {
            sqlite3_bind_text(stmt, nParm++, license, strlen(license), SQLITE_TRANSIENT);
        }
        // devNo
        sqlite3_bind_blob(stmt, nParm++, chedaojiNo, CheDaoJi_No_Len, SQLITE_TRANSIENT);
        // shotTimeBcd
        sqlite3_bind_blob(stmt, nParm++, picTimeBcd, BCD_TIME_LENGTH, SQLITE_TRANSIENT);
        // shotTime
        sqlite3_bind_text(stmt, nParm++, picTimeStr, strlen(picTimeStr), SQLITE_TRANSIENT);
        // camId
        sqlite3_bind_int(stmt, nParm++, camId);
        // fileNo
        sqlite3_bind_int(stmt, nParm++, fileNo);
        // gga
        sqlite3_bind_text(stmt, nParm++, ggaTmp, strlen(ggaTmp), SQLITE_TRANSIENT);
        // rmc
        sqlite3_bind_text(stmt, nParm++, rmcTmp, strlen(rmcTmp), SQLITE_TRANSIENT);

        int32_t stepRet = sqlite3_step(stmt);
        if (stepRet == SQLITE_DONE)
        {
            // LOG_INFO("sql（ %s, 车牌：%s ）插入成功", picName, license);
            ;
        }
        else
        {
            LOG_ERROR("sql（ %s, 车牌：%s ）插入出现错误：%s", picName, license, sqlite3_errmsg(db));
        }
    END:
        sqlite3_finalize(stmt);
    }
}

#define SQL_SELECT_PIC_NAME_NO_EXIT -1
#define isStrEqSqlCol(colName, sqlColName) (memcmp(colName, sqlColName, strlen(colName)) == 0)
static int8_t mapColToStruct(CamDataStruct *camArr, int32_t row, sqlite3_stmt *stmt, int32_t col_index)
{
    const int8_t *column_name = sqlite3_column_name(stmt, col_index);

    CamDataStruct *tmp = &camArr[row];

    int8_t result = SQLITE_OK;

    if (isStrEqSqlCol(TABLE_COL_PIC_NAME, column_name))
    {
        tmp->camData = CAM_NO_DATA;
        tmp->camDataLen = 0;

        const uint8_t *picNameWithNoJpg = sqlite3_column_text(stmt, col_index);

        uint8_t picAndPathName[PIC_FILENAME_MAX_LEN] = {0};
        sprintf(picAndPathName, "%s/%s.jpg", IMG_SAVE_DIR, picNameWithNoJpg);

        // 同步读取了，这个线程无所谓性能问题
        FILE *pf = fopen(picAndPathName, "r");
        if (pf == NULL)
        {
            LOG_ERROR("文件名：%s 打开错误：%s", picAndPathName, strerror(errno));
            updatePicRowAfterDelete((uint8_t *)picNameWithNoJpg);
            result = SQL_SELECT_PIC_NAME_NO_EXIT;
        }
        else
        {
            fseek(pf, 0, SEEK_END);
            int64_t fileSize = ftell(pf);
            tmp->camData = (uint8_t *)malloc(fileSize);
            rewind(pf);
            if (fread(tmp->camData, sizeof(uint8_t), fileSize, pf) != fileSize)
            {
                LOG_ERROR("读取文件错误：%s", strerror(errno));
                free(tmp->camData);
                result = SQL_SELECT_PIC_NAME_NO_EXIT;
            }
            tmp->camDataLen = fileSize;
            fclose(pf);
        }
        return result;
    }
    else if (isStrEqSqlCol(TABLE_COL_SHOT_TIME_BCD, column_name))
    {
        int32_t vLen = sqlite3_column_bytes(stmt, col_index);
        memcpy(tmp->timeBcd, sqlite3_column_blob(stmt, col_index), vLen);
    }
    else if (isStrEqSqlCol(TABLE_COL_CAM_ID, column_name))
    {
        tmp->camId = sqlite3_column_int(stmt, col_index);
    }
    else if (isStrEqSqlCol(TABLE_COL_PIC_TYPE, column_name))
    {
        tmp->picType = sqlite3_column_int(stmt, col_index);
    }
    else if (isStrEqSqlCol(TABLE_COL_FILE_NO, column_name))
    {
        tmp->uniqueTag = sqlite3_column_int(stmt, col_index);
    }

    return result;
}

#define SELECT_NUMS_ONE_TIME 10
SelectCamResult selectPicDb(uint8_t picType, uint8_t retryCount)
{
    if (!(picType == SMALL_PIC_TYPE || picType == BIG_PIC_TYPE || picType == CALIBRATION_PIC_TYPE))
    {
        LOG_ERROR("图片类型不正确");
        abort();
    }

    SelectCamResult res = {0};

    // SELECT * FROM `PicDb` WHERE `isBigPic` IS 1 AND isDelete IS 0 AND isUploaded IS 0  ORDER BY shotTime DESC LIMIT 0, 10;
    uint8_t sql[256];
    sprintf(sql,
            "SELECT * FROM `%s` WHERE `%s` IS %d AND %s IS 0 AND %s IS 0 AND %s IS %d ORDER BY %s DESC LIMIT 0, %d;", //
            PIC_DB_TABLE_NAME,
            TABLE_COL_PIC_TYPE,
            picType,
            TABLE_COL_IS_DELETE,
            TABLE_COL_IS_UPLOADED,
            TABLE_COL_RETRY_COUNTS,
            retryCount,
            TABLE_COL_SHOT_TIME,
            SELECT_NUMS_ONE_TIME);

    int32_t rc;
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
    if (rc != SQLITE_OK)
    {
        LOG_ERROR("数据库语句创建失败： %s", sqlite3_errmsg(db));
        goto END;
    }

    int32_t rows = 0;
    int32_t ncolumn = sqlite3_column_count(stmt);

    CamDataStruct *imgStructArr = (CamDataStruct *)malloc(sizeof(CamDataStruct) * SELECT_NUMS_ONE_TIME);
    // 查询一条出来
    int32_t stepRet;
    while ((stepRet = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        int8_t mapRes = SQLITE_OK;
        for (int32_t i = 0; i < ncolumn; i++)
        {
            int32_t vLen = sqlite3_column_bytes(stmt, i);
            int32_t vtype = sqlite3_column_type(stmt, i);
            const int8_t *column_name = sqlite3_column_name(stmt, i);

            switch (vtype)
            {
            case SQLITE_NULL:
            {
                // LOG_INFO("%s: null", column_name);
                break;
            }
            case SQLITE_INTEGER:
            {
                mapColToStruct(imgStructArr, rows, stmt, i);
                // LOG_INFO("%s: %d", column_name, sqlite3_column_int(stmt, i));
                break;
            }
            case SQLITE_FLOAT:
            {
                // LOG_INFO("%s: %f", column_name, sqlite3_column_double(stmt, i));
                break;
            }
            case SQLITE_BLOB:
            {
                mapColToStruct(imgStructArr, rows, stmt, i);
                // LOG_INFO("%s: BLOB", column_name);
                // hzlog_info(userLog, sqlite3_column_blob(stmt, i), vLen);
                break;
            }
            case SQLITE_TEXT:
            {
                // LOG_INFO("%s: %s", column_name, sqlite3_column_text(stmt, i));
                mapRes = mapColToStruct(imgStructArr, rows, stmt, i);
                break;
            }
            default:
            {
                LOG_INFO("%s: ERROR [%s]", column_name, sqlite3_errmsg(db));
                break;
            }
            }
            if (mapRes != SQLITE_OK)
            {
                break;
            }
        }
        rows++;
    }

    if (stepRet == SQLITE_DONE)
    {
        // LOG_INFO("sql查询成功");
        // if (rows == 0)
        // {
        //     LOG_INFO("sql查询结果为0");
        // }
    }
    else
    {
        LOG_ERROR("sql查询出现错误：%s", sqlite3_errmsg(db));
    }
    res.camArr = imgStructArr;
    res.len = rows;
END:
    sqlite3_finalize(stmt);
    return res;
}

// 删除一星期前的数据
#define DELETE_ROWS_BEFORE_DAYS 7
void deletePicRows()
{
    // DELETE FROM PicDb WHERE date('now', '-7 day') >= shotTime;
    // UPDATE PicDb SET shotTime = "2021-09-01 11:11:11" WHERE picName="000C292E3EF7FFFF123456762109030946339E0201";
    // DELETE FROM PicDb WHERE picName in (select picName from PicDb WHERE date('now', '-1 day') >= shotTime limit 10 );
    uint8_t sql[256];
    sprintf(sql,
            "DELETE FROM %s WHERE %s in (SELECT %s FROM %s WHERE date('now', '%d day') >= %s ORDER BY %s ASC  LIMIT 10);", //
            PIC_DB_TABLE_NAME,
            TABLE_COL_PIC_NAME,
            TABLE_COL_PIC_NAME,
            PIC_DB_TABLE_NAME,
            -DELETE_ROWS_BEFORE_DAYS,
            TABLE_COL_SHOT_TIME,
            TABLE_COL_SHOT_TIME);

    int32_t rc;
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
    if (rc != SQLITE_OK)
    {
        LOG_ERROR("数据库语句创建失败： %s", sqlite3_errmsg(db));
        goto END;
    }

    static uint16_t delNums = 0;
    if (sqlite3_step(stmt) == SQLITE_DONE)
    {
        delNums++;
        if (delNums > 100)
        {
            // LOG_INFO("删除数据库成功");
            delNums = 0;
        }
    }
    else
    {
        LOG_ERROR("sql删除出现错误：%s", sqlite3_errmsg(db));
    }
END:
    sqlite3_finalize(stmt);
}

void updateRetryCountAfterSendToQueue(uint8_t picName[PIC_FILENAME_MAX_LEN], uint8_t count)
{
    uint8_t sql[256];
    sprintf(sql,
            "UPDATE %s SET %s = %d WHERE %s=\'%s\';", //
            PIC_DB_TABLE_NAME,
            TABLE_COL_RETRY_COUNTS,
            count,
            TABLE_COL_PIC_NAME,
            picName);

    int32_t rc;
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
    if (rc != SQLITE_OK)
    {
        LOG_ERROR("数据库语句创建失败： %s", sqlite3_errmsg(db));
        goto END;
    }

    if (sqlite3_step(stmt) == SQLITE_DONE)
    {
        // LOG_INFO("更新（ %s: %s: 1 true ）数据库成功", picName, TABLE_COL_IS_UPLOADED);
        ;
    }
    else
    {
        LOG_ERROR("更新数据库失败：%s", sqlite3_errmsg(db));
    }
END:
    sqlite3_finalize(stmt);
}

void updatePicRowAfterUpload(uint8_t picName[PIC_FILENAME_MAX_LEN])
{
    uint8_t sql[256];
    sprintf(sql,
            "UPDATE %s SET %s = 1 WHERE %s=\'%s\';", //
            PIC_DB_TABLE_NAME,
            TABLE_COL_IS_UPLOADED,
            TABLE_COL_PIC_NAME,
            picName);

    int32_t rc;
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
    if (rc != SQLITE_OK)
    {
        LOG_ERROR("数据库语句创建失败： %s", sqlite3_errmsg(db));
        goto END;
    }

    if (sqlite3_step(stmt) == SQLITE_DONE)
    {
        // LOG_INFO("更新（ %s: %s: 1 true ）数据库成功", picName, TABLE_COL_IS_UPLOADED);
        ;
    }
    else
    {
        LOG_ERROR("更新数据库失败：%s", sqlite3_errmsg(db));
    }
END:
    sqlite3_finalize(stmt);
}

void updatePicRowAfterDelete(uint8_t *picName)
{
    uint8_t sql[256];
    sprintf(sql,
            "UPDATE %s SET %s = 1 WHERE %s=\'%s\';", //
            PIC_DB_TABLE_NAME,
            TABLE_COL_IS_DELETE,
            TABLE_COL_PIC_NAME,
            picName);

    int32_t rc;
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
    if (rc != SQLITE_OK)
    {
        LOG_ERROR("数据库语句创建失败： %s", sqlite3_errmsg(db));
        goto END;
    }

    if (sqlite3_step(stmt) == SQLITE_DONE)
    {
        // LOG_INFO("更新（ %s: %s: 1 true ）数据库成功", picName, TABLE_COL_IS_DELETE);
        ;
    }
    else
    {
        LOG_ERROR("更新数据库失败：%s", sqlite3_errmsg(db));
    }
END:
    sqlite3_finalize(stmt);
}

void closePicDb(uv_work_t *handle, int32_t status)
{
    LOG_INFO("关闭数据库");
    sqlite3_close(db);
}
